package com.thinkgem.jeesite.modules.crawler.utils;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 操作excel
 * @author mj 2017/5/18
 *
 */
public class ExcelUtils {
	private static Integer COLUMN_SIZE = 5;
	private static final Integer COLUMN_LENGTH = 22;
	private static final Integer COLUMN_WIDTH = 18;
	
	
	/**
	 * 生成excel
	 * @author mj 2017/5/18
	 * @param  taskDataRepoList
	 * @param  desFilePath
	 * @return
	 */
	public static void createExcel(String title, List<String> tableHeaderList, List<Map> dataMapList, String desFilePath) {
		if(StringUtils.isBlank(title) || (title.length() > 31)) {
			title = "开放数据集";
		}
		//创建HSSFWorkbook对象  
		HSSFWorkbook wb = new HSSFWorkbook();  
		//创建HSSFSheet对象  
		HSSFSheet sheet = wb.createSheet(title);  
		//获取列长度
		int column = 0;
		column = getColumnSize(tableHeaderList, dataMapList);
		if(column != 0) {
			COLUMN_SIZE = column;
		}
		for(int i=0; i<COLUMN_SIZE; i++) {
			sheet.setColumnWidth(i, 256 * 30); 
		}
		//头部一二行
		createHeader(title, tableHeaderList, sheet, wb);
		//后面的部分
		createBody(dataMapList, sheet, wb);
		FileOutputStream output;
		try {
			output = new FileOutputStream(desFilePath);
			wb.write(output);  
			output.flush();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	
	/**
	 * 获取列长度
	 * @author mj 2017/7/18
	 * @param  tableHeaderList
	 * @param  dataMapList
	 * @return culumnSize
	 */
	public static int getColumnSize(List<String> tableHeaderList, List<Map> dataMapList) {
		int culumnSize = 0;
		//如果表头列表不为空
		if(CommonListUtils.isNotEmpty(tableHeaderList)) {
			culumnSize = tableHeaderList.size();
			return culumnSize; 
		}
		//如果数据不为空
		if(CommonListUtils.isNotEmpty(dataMapList)) {
			for(Map map : dataMapList) {
				if(map != null) {
					culumnSize = map.size();
					break;
				}				
			}//end for	
		}
		return culumnSize;
	}
	
	
	/**
	 * 创建头部，包括第一行和第二行
	 * @author mj 2017/5/18
	 * @param  title
	 * @param  sheet
	 * @return
	 */
	public static void createHeader(String title, List<String> tableHeaderList, HSSFSheet sheet, HSSFWorkbook wb) {
		if(sheet == null || wb == null) {
			return;
		}
		if(StringUtils.isBlank(title)) {
			title = "开放数据集";
		}
		//表头的长度
		int headerLen = 0;
		if(CommonListUtils.isNotEmpty(tableHeaderList)) {
			headerLen = tableHeaderList.size();
		}
		//第一行
//		HSSFRow row0 = sheet.createRow(0);
//		row0.setHeightInPoints(28);
//		HSSFCell cell0 = row0.createCell(0);//创建一个单元格
//		HSSFFont fontStyle1 = wb.createFont();
//		HSSFCellStyle cellStyle1 = wb.createCellStyle();
//		setStyle1(fontStyle1, cellStyle1);
//		//合并单元格
//		CellRangeAddress cellRangeAddress  = new CellRangeAddress(
//				0,
//				0,
//				0,
//				COLUMN_SIZE - 1);
//	    sheet.addMergedRegion(cellRangeAddress);
//        cell0.setCellValue(title);
//        cell0.setCellStyle(cellStyle1);//设置单元格样式
//        setRegionStyle(sheet, cellRangeAddress, cellStyle1);//合并重新设置边框样式
        //前面固定部分
        HSSFRow row1 = sheet.createRow(0);
        row1.setHeightInPoints(18);
        HSSFFont fontStyle2 = wb.createFont();
		HSSFCellStyle cellStyle2 = wb.createCellStyle();
		setStyle2(fontStyle2, cellStyle2);
        for(int j=0;j<COLUMN_SIZE;j++){//固定列
            HSSFCell cell = row1.createCell(j);
            String head = "未确定表头名";
            if(j < headerLen) {
            	head = tableHeaderList.get(j);
            }
            cell.setCellValue(head);
            cell.setCellStyle(cellStyle2);//设置单元格样式
        }
	}
	
	/**
	 * 创建数据部分
	 * @author mj 2017/5/18
	 * @param  title
	 * @param  sheet
	 * @return
	 */
	public static void createBody(List<Map> dataMapList, HSSFSheet sheet, HSSFWorkbook wb) {
		if(sheet == null || wb == null || CommonListUtils.isEmpty(dataMapList)) {
			return;
		}
		//单元格样式
		HSSFFont fontStyle3 = wb.createFont();
		HSSFCellStyle cellStyle3 = wb.createCellStyle();
		setStyle3(fontStyle3, cellStyle3);
		//从第二行开始
		int rowStartIndex = 1;
		for(int i=0; i<dataMapList.size(); i++) {
			Map dataMap = dataMapList.get(i);
			if(dataMap == null) {
				continue;
			}
			//遍历map
			Iterator iterator = dataMap.entrySet().iterator();
			int j = 0;
			int rowIndex = i + rowStartIndex;
			//创建单元格
			HSSFRow row = sheet.createRow(rowIndex);
			row.setHeightInPoints(18);
			while(iterator.hasNext()) {
				Entry entry = (Entry) iterator.next();
				Object value = entry.getValue();
				//创建单元格
				HSSFCell cell = row.createCell(j++);
				cell.setCellValue(value.toString());
				cell.setCellStyle(cellStyle3);//设置单元格样式
			}//end while
			if(j < COLUMN_SIZE) {
				for(int k = j; k < COLUMN_SIZE; k++) {
					HSSFCell cell = row.createCell(k);
					cell.setCellStyle(cellStyle3);//设置单元格样式
				}
			}//end if
		}      
	}
	
	/**
	 * 第一行的样式
	 * @author mj 2017/5/18
	 * @param  fontStyle
	 * @param  cellStyle
	 * @return
	 */
	public static void setStyle1(HSSFFont fontStyle, HSSFCellStyle cellStyle) {
		if(fontStyle == null || cellStyle == null) {
			return;
		}
		//设置字体
		//设置字体样式  		
		fontStyle.setFontName("宋体");  
		//设置字体高度  		  
		fontStyle.setFontHeightInPoints((short)20);    
		//设置粗体  		
	    fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	    cellStyle.setFont(fontStyle);
	    //设置单元格样式
	    // 设置单元格的横向和纵向对齐方式，具体参数就不列了，参考HSSFCellStyle  	   
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  	     
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    // 这里仅设置了底边边框，左边框、右边框和顶边框同理可设  
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
	}
	
	/**
	 * 第二行的样式
	 * @author mj 2017/5/18
	 * @param  fontStyle
	 * @param  cellStyle
	 * @return
	 */
	public static void setStyle2(HSSFFont fontStyle, HSSFCellStyle cellStyle) {
		if(fontStyle == null || cellStyle == null) {
			return;
		}
		//设置字体
		//设置字体样式  		
		fontStyle.setFontName("宋体");  
		//设置字体高度  		  
		fontStyle.setFontHeightInPoints((short)11);    
		//设置粗体  		
	    fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	    cellStyle.setFont(fontStyle);
	    //设置单元格样式
	    // 设置单元格的横向和纵向对齐方式，具体参数就不列了，参考HSSFCellStyle  	   
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  	     
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    // 这里仅设置了底边边框，左边框、右边框和顶边框同理可设  
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
	}
	
	/**
	 * 其他行的样式
	 * @author mj 2017/5/18
	 * @param  fontStyle
	 * @param  cellStyle
	 * @return
	 */
	public static void setStyle3(HSSFFont fontStyle, HSSFCellStyle cellStyle) {
		if(fontStyle == null || cellStyle == null) {
			return;
		}
		//设置字体
		//设置字体样式  		
		fontStyle.setFontName("宋体");  
		//设置字体高度  		  
		fontStyle.setFontHeightInPoints((short)11);    
		//设置粗体  		
//	    fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	    cellStyle.setFont(fontStyle);
	    //设置单元格样式
	    // 设置单元格的横向和纵向对齐方式，具体参数就不列了，参考HSSFCellStyle  	   
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  	     
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    // 这里仅设置了底边边框，左边框、右边框和顶边框同理可设  
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
	    cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
	}
	
    /**
     * 设置合并单元格边框
     * @author mj 2016/12/29
     * @param  sheet
     * @param  cellRangeAddress
     * @param  cs
     * @return
     */
    @SuppressWarnings("deprecation")
    private static void setRegionStyle(HSSFSheet sheet, CellRangeAddress cellRangeAddress , HSSFCellStyle cs) {
        for (int i = cellRangeAddress.getFirstRow(); i <= cellRangeAddress.getLastRow(); i ++) {
            HSSFRow row = sheet.getRow(i)==null?sheet.createRow(i):sheet.getRow(i);
            for (int j = cellRangeAddress.getFirstColumn(); j <= cellRangeAddress.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j)==null?row.createCell(j):row.getCell(j);
                cell.setCellStyle(cs);
            }
        }
    }
}
